{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 爬取网页雪球的数据，每次请求200条数据\n",
    "import time\n",
    "import requests\n",
    "from lxml import etree\n",
    "import pandas as pd\n",
    "import os\n",
    "from concurrent.futures import ThreadPoolExecutor\n",
    "headers= {\n",
    "    \"user-agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36\",\n",
    "    \"cookie\":\"cookiesu=171726208053581; device_id=f902a69c713622f6a8395f0617635722; smidV2=2024091314141439f5894dee216ffb4994f0658984d41e003a7feb386391ac0; Hm_lvt_1db88642e346389874251b5a1eded6e3=1726208055; HMACCOUNT=809DACAB4775E9FD; remember=1; xq_a_token=57fb512afe3c3c0b14598427b76e98dbfaf721c4; xqat=57fb512afe3c3c0b14598427b76e98dbfaf721c4; xq_id_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJ1aWQiOjkzODc5MjczMzgsImlzcyI6InVjIiwiZXhwIjoxNzI4ODAwOTA3LCJjdG0iOjE3MjYyMDg5MDc1NDMsImNpZCI6ImQ5ZDBuNEFadXAifQ.fx4hxaqkIT5II-EGOCeSvsGyiNyl2djdh2n1N1b3KAokHs9l8fbzc_bKKFg68UbwB-KUfBso3EUdHI8BBkviiLgZ7AHeiwbS9KE3yu51FLkuSrDeCF5OuvFvGoBNaW4OeIz31i2MpWkg6j0m1X4FVMLSNBEKgcI0qjtNgDLtpINywE2PkO9cawvCyGDVmDmQBCLYxDVGC6nCGgNP_HiCKmbg-MikamimAxGDgA5ffgYXP-a1acJPvwv8KX8P4GSEWFhYp0SgSWyi4I8K49ICSP5fhBOFp0D2DNn6TKnXDEXSxIAYn8-l0zansHe8Msm4CdTzEwuXFFDkLNIUq6LWAQ; xq_r_token=d5db3c6f9794606ffd59e8659caf4d1252b535c7; xq_is_login=1; u=9387927338; acw_tc=1a0c63db17262098584105876e0032d7b828b71cf7c97268fc4ff44a17660d; .thumbcache_f24b8bbe5a5934237bbc0eda20c1b6e7=oDo8xPOP1wrseAloZZt4Xuh6CJLpR21rAYDmKzs4IOH8pbn9rWBhQXer941ODiWtIw+0XIWrOKPxyIgI2ABtBg%3D%3D; Hm_lpvt_1db88642e346389874251b5a1eded6e3=1726209876; ssxmod_itna=Yq+xyQit0QF49DGxBcDBbntIv2kexZlDGqD=eTNDc7Rx052SeGzDAxn40iDt=hk+ePiqFPOcfHKHWOpm7IQPo9qTd+6G0cEWzQx0aDbqGkd97z4GGBxBYDQxAYDGDDPDo=KD1D3qDkD7rtlBLNSDi3DbrtDf4DmDGYg7qDgDYQDGuI0D7QDITtqQY+S+DDl+DcYbuDhWl20ICX=ukh5oGDgD0UtxBLaB1ndMZyY4l28XP2H7qGy7KGuSkNx5kTdfkiVij4TzODHK+mobbd4YoxEFAfoWHdcZ0ho77PrsHlWUpwo+DDAYY+WYtTYD; ssxmod_itna2=Yq+xyQit0QF49DGxBcDBbntIv2kexZlDGqD=eTNDc74nIM0DDsYLDLQmTb07QqRn6PNYQQ4iToUO+nRPYoYhEEY5vxApxiXh=r4LlQl7IrLIFjD74C7o0MSFCK=nL8F4rnpV39EFh3LrUxim08p5Ugo4eCYP=ErqK+KjFY5C4nAPz4ohQrvDpl6BUhXP7WYoTkKhbCx8nrATxSKQrRb4nmrwQpKLQfpR3ZKqGenbcLQdxprbn23P3qr4X8WT8EnSYLb10tiipci0S+m+L=+slbAtSkDbYovYGNijf+Hv5+2ST71NRohjQGL=ed=dAEEA7mSa6NnplWimQGanK4j4Y4eoSipW=47oji+4fIPnKya3OmPCDAamPdxY325iOYCadYaofOi5tmeiw39Pt=qlaE4xmajmShoW2GUlGF6dhBKzcqlAeC72bgjabDOAAhfcepvgPp6WfCa6Yld6zp57EXCPi8beFqwQUDh6+B9FgfACF8toFpYiBF5Eaz3d4FKogR8CujfElaKDgHoOdGWA5XDhWnLGOIluyEYZ+veDNXCfuO2jWts9vxSI7+51BKDENookFyPor+KwMVEQ5ffkak+BytIANK8W7C4lo4fIRIDwAeDSI2Cwc9twjvN2RCUPZEiwYxlAl/WvUetlAOVnUfLPkg4DQIWGtNVax+gXzDZdqXUVrBaeBCvhxQ53aPoqmDV2ee7EAWeYma3v4A0OaW5xipa5pFE2Sexi4snrlpexN9qOGhixnj40qnxDLxD+heLGzePYiOmDeADD\"\n",
    "}\n",
    "\n",
    "def get_current_price(stock_code, stock_type):\n",
    "    url = f\"https://xueqiu.com/S/{stock_type+stock_code}\"\n",
    "    res = requests.get(url=url,headers=headers)\n",
    "    if res.status_code == 200:\n",
    "        page_data = res.content.decode(\"utf-8\")\n",
    "        tree = etree.HTML(page_data)\n",
    "        stock_name = tree.xpath('//h1[@class=\"stock-name\"]//text()')\n",
    "        # td_list = tree.xpath('//table[@class=\"quote-info\"]//tr//td')\n",
    "        current_price = tree.xpath('//div[@class=\"stock-current\"]//strong//text()')\n",
    "        #print(stock_code+\": \"+current_price[0])\n",
    "        price = current_price[0]\n",
    "        if price.find(\"¥\")!=-1:\n",
    "            price = price.replace(\"¥\",\"\")\n",
    "        if price.find(\"HK$\")!=-1:\n",
    "            price = float(price.replace(\"HK$\",\"\"))*0.91\n",
    "            price = str(round(price,2))\n",
    "        #print(price)\n",
    "        time.sleep(0.1)\n",
    "        return price\n",
    "        #td_lst = tree.xpath('//div[@class=\"quote-container\"]//table[@class=\"quote-info\"]/tr//td')\n",
    "    else:\n",
    "        return None\n",
    "rs = pd.DataFrame()\n",
    "output_file = r\"D:\\python\\salaryProject\\dyf\\zq_data\\resource\\待查询的数据表\\待查询的数据表2\\2\"\n",
    "stock_type = \"SZ\"\n",
    "for root, dirs, files in os.walk(output_file):\n",
    "    for file in files:\n",
    "        file_path = os.path.join(root,file)\n",
    "        print(file_path)\n",
    "        df = pd.read_csv(file_path,dtype=str)\n",
    "        with ThreadPoolExecutor(max_workers=20) as executor:\n",
    "            # 提交任务到线程池\n",
    "            prices = list(executor.map(lambda x: get_current_price(str(x), stock_type), df['证券代码']))\n",
    "            # 将获取的价格添加到DataFrame中\n",
    "        df['当前价格'] = prices\n",
    "        rs = pd.concat([rs,df])\n",
    "        print(df.info())\n",
    "        time.sleep(60)\n",
    "print(rs.info())\n",
    "rs.to_excel('../resource/证券代码查询结果2.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 批量调取tushare调用api获取数据\n",
    "import time\n",
    "import tushare as ts\n",
    "import pandas as pd\n",
    "import os\n",
    "from concurrent.futures import ThreadPoolExecutor, as_completed\n",
    "\n",
    "# 设置tushare token\n",
    "ts.set_token('0d9223de9a848ebf6f2e268039b1762919bfbcb1826df44246220d4c')\n",
    "pro = ts.pro_api()\n",
    "\n",
    "date_str = '20240913'\n",
    "\n",
    "# 从Excel中读取证券代码\n",
    "# df = pd.read_excel(\"../resource/证券代码数据.xlsx\", dtype=str, sheet_name=\"Sheet3\")\n",
    "\n",
    "# output_file = r\"D:\\python\\salaryProject\\dyf\\zq_data\\resource\\待查询的数据表\\待查数据-400\"\n",
    "\n",
    "\n",
    "df = pd.read_excel(\"../resource/查询账户证券持有余额20240814.xlsx\",dtype=str)\n",
    "\n",
    "df = df[['证券代码','证券简称']]\n",
    "df = df.drop_duplicates(subset=['证券代码'])\n",
    "df['股票代码'] = df['证券代码'].apply(lambda x:str(x)+\".SZ\")\n",
    "results = []\n",
    "data_length = df.shape[0]\n",
    "df_nums = (data_length//1000)+(1 if data_length%1000>0 else 0)\n",
    "print(df_nums)\n",
    "# 定义获取的时间\n",
    "date_str = '20240913'\n",
    "for i in range(0,df_nums):\n",
    "    # 截取代码片段数据\n",
    "    start_row = i*1000\n",
    "    end_row = min(start_row+1000,data_length)\n",
    "    results.append(df.iloc[start_row:end_row])\n",
    "# 遍历数据所有片段\n",
    "rs = pd.DataFrame()\n",
    "for result in results:\n",
    "    ts_code_list = result['股票代码'].to_list()\n",
    "    ts_codes = \",\".join(ts_code_list)\n",
    "    df_tmp = pro.daily(trade_date=date_str, end_date=date_str, ts_code=ts_codes)\n",
    "    rs = pd.concat([rs,df_tmp])\n",
    "    print(df_tmp.info())\n",
    "print(rs.info())\n",
    "rs.to_excel(\"../resource/tushare.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "login success!\n",
      "login respond error_code:0\n",
      "login respond  error_msg:success\n"
     ]
    }
   ],
   "source": [
    "import baostock as bs\n",
    "import pandas as pd\n",
    "from concurrent.futures import ThreadPoolExecutor\n",
    "def get_code_price(code,data_str):\n",
    "    rs = bs.query_history_k_data_plus(code,\n",
    "    \"date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST\",\n",
    "    start_date=date_str, end_date=date_str,\n",
    "    frequency=\"d\", adjustflag=\"3\")\n",
    "    return rs.get_row_data()\n",
    "#### 登陆系统 ####\n",
    "lg = bs.login()\n",
    "# 显示登陆返回信息\n",
    "print('login respond error_code:'+lg.error_code)\n",
    "print('login respond  error_msg:'+lg.error_msg)\n",
    "ex_file_path = r\"D:\\fileData\\python\\input\\证券数据\\证券合并数据.xlsx\"\n",
    "df = pd.read_excel(ex_file_path,dtype=str)\n",
    "df['股票代码'] = df['证券代码'].apply(lambda x:\"sz.\"+str(x))\n",
    "date_str = '2024-09-13'\n",
    "data_list = []\n",
    "results = []\n",
    "with ThreadPoolExecutor(max_workers=15) as executor:   \n",
    "    results = executor.map(get_code_price, df['股票代码'],date_str)   \n",
    "for result in results:  \n",
    "    data_list.append(result) \n",
    "result = pd.DataFrame(data_list,columns=[\"date\",\"code\",\"open\",\"high\",\"low\",\"close\",\"preclose\",\"volume\",\"amount\",\"adjustflag\",\"turn\",\"tradestatus\",\"pctChg\",\"isST\"])\n",
    "print(result.info())\n",
    "#### 获取沪深A股历史K线数据 ####\n",
    "# 详细指标参数，参见“历史行情指标参数”章节；“分钟线”参数与“日线”参数不同。“分钟线”不包含指数。\n",
    "# 分钟线指标：date,time,code,open,high,low,close,volume,amount,adjustflag\n",
    "# 周月线指标：date,code,open,high,low,close,volume,amount,adjustflag,turn,pctChg\n",
    "# rs = bs.query_history_k_data_plus(\"sh.600000\",\n",
    "#     \"date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST\",\n",
    "#     start_date='2024-09-13', end_date='2024-09-13',\n",
    "#     frequency=\"d\", adjustflag=\"3\")\n",
    "# print('query_history_k_data_plus respond error_code:'+rs.error_code)\n",
    "# print('query_history_k_data_plus respond  error_msg:'+rs.error_msg)\n",
    "\n",
    "# #### 打印结果集 ####\n",
    "# data_list = []\n",
    "# while (rs.error_code == '0') & rs.next():\n",
    "#     # 获取一条记录，将记录合并在一起\n",
    "#     data_list.append(rs.get_row_data())\n",
    "# result = pd.DataFrame(data_list, columns=rs.fields)\n",
    "\n",
    "# #### 结果集输出到csv文件 ####   \n",
    "# result.to_csv(\"D:\\\\history_A_stock_k_data.csv\", index=False)\n",
    "# print(result)\n",
    "\n",
    "#### 登出系统 ####\n",
    "bs.logout()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
